﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using Sunny.UI;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using DaLianJinTouClient;
using NPOI.POIFS.Crypt.Dsig;
using AForge.Controls;
using iTextSharp.text;
using NPOI.SS.Formula.Functions;
using System.Diagnostics;

namespace TestProject
{
    public partial class frmImportExport : UIPage
    {
        DbContexts db = new DbContexts();

        DataTable dt = new DataTable();

        List<Sys_UserInfo1> list = new List<Sys_UserInfo1>();

        public frmImportExport()
        {
            InitializeComponent();
        }

        private void frmImportExport_Load(object sender, EventArgs e)
        {
            dt = db.Queryable<Sys_UserInfo1>().ToDataTable();
            //绑定数据源 
            BindingSource bs = new BindingSource();
            bs.DataSource = dt;
            uiDataGridView1.DataSource = bs;
        }

        #region 导出
        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btn_Export_Click(object sender, EventArgs e)
        {
            //文件名
            string FileName = DateTime.Now.SecondsSince1970().ToString();
            SaveFileDialog saveFileDialog = new SaveFileDialog
            {
                //设置文件标题
                Title = "导出Excel文件",
                //设置文件类型
                Filter = "Excel 工作簿(*.xlsx)|*.xlsx|Excel 97-2003 工作簿(*.xls)|*.xls",
                //设置默认文件类型显示顺序
                FilterIndex = 1,
                //是否自动在文件名中添加扩展名
                AddExtension = true,
                //是否记忆上次打开的目录
                RestoreDirectory = true,
                //设置默认文件名
                FileName = FileName
            };

            if (saveFileDialog.ShowDialog() != DialogResult.OK)
            {
                return;
            }
            else
            {
                //获得文件路径
                string localFilePath = saveFileDialog.FileName.ToString();

                //导出
                ExportExcel(dt, localFilePath);

                //打开资源管理器并选中文件
                Process.Start("explorer.exe", "/select," + localFilePath);
            }
        }

        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <param name="dt"></param>
        public static void ExportExcel(DataTable dt,string filepath)
        {
            ExcelHelper.DataTableToExcel(dt, filepath);
        }
        #endregion

        #region 导入
        /// <summary>
        /// 导入Excel
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btn_Import_Click(object sender, EventArgs e)
        {
            //清空数据
            uiDataGridView1.ClearAll();

            //创建OpenFileDialog
            OpenFileDialog opnDlg = new OpenFileDialog();
            //为图像选择一个筛选器
            opnDlg.Filter = "Excel 工作簿(*.xlsx)|*.xlsx|Excel 97-2003 工作簿(*.xls)|*.xls";
            //设置对话框标题
            opnDlg.Title = "打开图像文件";
            //启用帮助按钮
            opnDlg.ShowHelp = true;
            //不可选择多个图片文件
            opnDlg.Multiselect = false;

            //如果选择的结果为打开，则选定文件
            if (opnDlg.ShowDialog() == DialogResult.OK && opnDlg.FileName != string.Empty)
            {
                //读取当前选中的文件地址
                var curFileName = opnDlg.FileName;

                var  dt1 = ImortExcel(curFileName);
                if (dt1.Rows.Count > 0)
                {
                    //绑定数据到列表
                    uiDataGridView1.DataSource = dt1;
                }
            }
            else
            {
                return;
            }
        }

        /// <summary>
        /// 导入Excel
        /// </summary>
        /// <param name="filepath">excel文件路径</param>
        /// <returns></returns>
        public static DataTable ImortExcel(string filepath)
        {
            return ExcelHelper.ExcelToDataTable(filepath);
        }
        #endregion

        /// <summary>
        /// excel数据导入数据库
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void btn_ExcelToDb_Click(object sender, EventArgs e)
        {
            ShowWarningTip("暂未实现");
        }
    }
}
